package reports;

import java.io.Writer;
import java.sql.*;
import java.util.*; 

import db.AbstractDAO;
import db.AbstractDbRequest;
import db.AbstractDbResponse;

public class Report extends AbstractDAO {

	public Report() throws ClassNotFoundException, SQLException {
		super();
	}

	@Override
	public AbstractDbResponse execute(AbstractDbRequest dbRequest) {
		return null;
	}
	
	public TreeSet<String> getYearsToSearch() throws SQLException {
		
		TreeSet<String> dates = new TreeSet<String>(); 
		String query = new String();
	    Statement s = conn.createStatement();
	    
		query = "select distinct c.orderdate from customerorder c order by c.orderdate";  
		ResultSet r = null; 
        
        try {
        	r = s.executeQuery(query);
        	while (r.isAfterLast() == false) {
        		r.next();
        		dates.add(r.getString(1).substring(0, 4)); 
 
        	} 
        }
        catch (SQLException e) {
        	r.close(); 
        	s.close(); 
        	closeConnection(); 
        	return dates; 
        }

    	r.close(); 
    	s.close(); 
    	closeConnection();
        return dates; 
	}

	public void closeConnection() {
		try {
			conn.close();
		}
		catch (SQLException e) {}
	}

	public HashMap<String, ArrayList<Double>> getData(String startDate, String startMonth, String startYear, String endDate, String endMonth, String endYear, String orderType) throws SQLException {
		
		HashMap<String, ArrayList<Double>> data = new HashMap<String, ArrayList<Double>>(); 
		String query = new String();
		query = "select c.pid, c.quantity from customerorder c where c.orderType = '" + orderType + "' and (c.orderdate >=to_date('" + startDate + "-" + startMonth + "-" + startYear +"') AND c.orderdate <= to_date('" + endDate + "-" + endMonth + "-" + endYear + "'))";  
		
		ResultSet r = null; 
	    Statement s = conn.createStatement(); 
		
	    try {
	    	r = s.executeQuery(query); 
	    	while (r.isAfterLast() == false) {
	    		r.next(); 
	    		String pid = r.getString(1); 
	    		double quantity = r.getInt(2); 
	    		
	    		if (data.containsKey(pid)) {
		    		ArrayList<Double> qData = data.get(pid); 
		    		qData.set(0, qData.get(0) + quantity); 
	    		}
	    		else {
		    		ArrayList<Double> qData = new ArrayList<Double>(); 
		    		qData.add(quantity); 
		    		data.put(pid, qData); 
	    		}
	    	}
	    }
	    catch (SQLException e) {
	    	
	    }
	    
	    TreeSet<String> keys = (TreeSet<String>) data.keySet(); 
	    Iterator itr = keys.iterator(); 
	    while (itr.hasNext()) {
	    	String currentPid = (String) itr.next(); 
	    	query = "select p.price from product p where p.pid ='" + currentPid + "'"; 
	    	try {
	    		r = s.executeQuery(query); 
	    		r.next(); 
	    		ArrayList<Double> prodData = data.get(currentPid); 
	    		double price = r.getDouble(1); 
	    		prodData.add(price); 
	    		data.put(currentPid, prodData); 
	    	}
	    	catch (SQLException e) {
	    		
	    	}
	    }
	    	
		return data; 
	}
 
}
